import os
import sys
import pandas as pd

# import numpy as np
import openpyxl
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment, colors
from openpyxl.utils import get_column_letter
from warnings import simplefilter

# import xlwings as xw


simplefilter(action="ignore", category=FutureWarning)  # warnings??

argv = sys.argv  # sys??
path_train = argv[1]  # ??
data_load = pd.read_csv(path_train)  # ??
print(20 * "*")
file_name = os.path.splitext(os.path.basename(path_train))[0]  # ??
print(file_name)
print(20 * "*")

# 定义Title字段
title_list = {
    "handle baseband updater": "U5501_RF",  ##dict
    "BBUpdater error 3 - Fail to create transport": "U5501_RF",
    "BBUpdater error 3 - Failed image download": "U5501_RF",
    "BBUpdater error 3 - Failed sending EBL": "U5501_RF",
    "BBUpdater error 3 - Failed loading loader": "U5501_RF",
    "BBUpdater error 3 - Fail to bootup baseband: kBBUReturnIOError": "U5501_RF",
    "BBUpdater error 3 - Failed executing secure mode, failing program": "U5501_RF",
    "BBUpdater error 3 - Failed reading PSI-RAM Running status": "U5501_RF",
    "BBUpdater error 3 - Failed querying chip information in the first stage": "U5501_RF",
    "BBUpdater error 3 - Failed gathering personalization parameters @ second stage: kBBUReturnIOError": "U5501_RF",
    "BBUpdater error 9 - PSI is not running enhanced mode, unsupported firmware": "U5501_RF",
    "BBUpdater error 12 - Failed gathering personalization parameters @ second stage": "U5501_RF",
    "BBUpdater error 12 - Failed executing secure mode, failing program": "U5501_RF",
    "BBUpdater error 15 - Failed image download!": "U5501_RF",
    "BBUpdater error 18 - Failed running eUICC: kBBUReturnFatalError": "U5501_RF",
    "BBUpdater error 24 - Failed to finalize: kBBUReturnFusingError": "U5501_RF",
    "BBUpdater error 46 - Timeout on waiting for BB init status": "U5501_RF",
    "BBUpdater error 49 - Baseband crashed during ping": "U5501_RF",
    "BBUpdater error 51 - Fail to create transport kBBUReturnFailCreateTransportSecond": "U5501_RF",
    "BBUpdater error 50 - Fail to create transport: kBBUReturnFailCreateTransportFirst": "U5501_RF or FL1001",
    "BBUpdater error 58 - Failed to finalize: kBBUReturnFailedSetNVItems": "U5501_RF",
    "BBUpdater error 59 - Failed to finalize: kBBUReturnFailedSetSVN": "U5501_RF",
    "BBUpdater error 61 - Failed to finalize: kBBUReturnFailedNVSync": "U5501_RF",
    "BBUpdater error 65 - Failed image download!": "U5501_RF",
    "SEUpdaterExecCommand": "U7500",
    "SEUpdaterCreate": "U7500",
    "SE failed": "U7500",
    "failed to pair stockholm": "U7500",
    "NFRestoreNfcForController": "U7500",
    "NFRestoreNfcFirmwareStep": "U7500",
    "NFRestoreJcop": "U7500",
    "NFRestoreNfc": "U7500",
    "NfSecureElementUpdateOSInfo": "U7500",
    "RoseRestore error": "U6500",
    "Rose failed": "U6500",
    "RoseUpdaterCreate": "U6500",
    "Restore timeout error": "UNKOWN",
    "connection problem with USB host": "UNKOWN",
    "got http failures": "UNKOWN",
    "transition from": "UNKOWN",
    "update_aht_block_invoke: coronet-uart update failed": "UNKOWN",
    "update_aht_block_invoke: rtp-transport update failed": "UNKOWN",
    "Failed to handle FUD data request": "UNKOWN",
    "Failed to handle all flash request": "UNKOWN",
    "Failed to handle EAN data request": "UNKOWN",
    "failed to send source boot object file to device": "UNKOWN",
    "Failed to detect DFU mode within 120s": "UNKOWN",
    "Failed to detect device debug usb within 120s": "UNKOWN",
    "Failed to restore device in DFU mode": "UNKOWN",
    "Failed to restore device in recovery mode": "UNKOWN",
    "failed to update syscfg": "UNKOWN",
    "failed aru pre seal tasks": "UNKOWN",
    "failed to clean NAND": "UNKOWN",
    "failed SEP device certification": "UNKOWN",
    "failed Lynx certification": "UNKOWN",
    "failed to restore APFS image": "UNKOWN",
    "Failed to handle image request": "UNKOWN",
    "Failed to copy auth install options in TAD mode": "UNKOWN",
    "Failed to send directory root to device": "UNKOWN",
    "Failed to copy auth install options in DFU mode": "UNKOWN",
    "Failed to handle diags data request": "UNKOWN",
    "find device hardwareModel through device_map.plist": "UNKOWN",
    "Device added from invalid state Restoring": "UNKOWN",
    "RestoreOS device removed before restored completed": "UNKOWN",
    "update_NAND: failed to query config string from nvmefwupdater": "UNKOWN",
    "update_NAND: failed to update NAND update file": "UNKOWN",
    "update_NAND: NAND update failed": "UNKOWN",
    "Unexpected device state": "UNKOWN",
    "miss rcrt": "UNKOWN",
    "miss mcrt": "UNKOWN",
}


# 完成triage
def substrings_in_string(big_string, substrings):
    for substring in substrings:
        # -1是没有匹配到
        if big_string.find(substring) != -1:
            return substring
    print(big_string)
    return "other"


data_load["TRIAGE"] = data_load["Fail Message"].map(
    lambda x: substrings_in_string(x, title_list)
)
data_load["Component"] = None

# 此处需要新增Component columns

for i in range(len(data_load["TRIAGE"])):
    key_found = data_load["TRIAGE"][i]
    # print(key_found)
    # print(20*'*')
    Component_found = title_list.get(key_found)
    # print(Component_found)
    # for componet in Component_found:
    data_load["Component"][i] = Component_found
    # data_load.insert(data_load.shape[1],'Component',Component_found)

    # print(key_found)
    # data_load['component']=title_list.get(key_found)
outname = file_name + "_TRIAGE.xlsx"
outdir = os.path.abspath(os.path.join(os.path.dirname(__file__)))
fullname = os.path.join(outdir, outname)
writer = pd.ExcelWriter(fullname)
data_load.to_excel(writer, sheet_name="triage_raw_data", encoding="utf-8")

writer.save()


# 写入excel
data = pd.read_excel(fullname, engine="openpyxl")
pivot_qty = pd.pivot_table(
    data,
    index=["TRIAGE"],
    columns=["Product Code"],
    values=["Serial Number"],
    aggfunc="count",
    sort=True,
    margins=True,
)
pivot_qty.to_excel(writer, sheet_name="pivot_qty")
pivot_SN = pd.pivot_table(
    data,
    index=["TRIAGE", "Product Code", "Serial Number"],
    values=["Test"],
    aggfunc="count",
)
pivot_SN.to_excel(writer, sheet_name="pivot_sn")
pivot_component = pd.pivot_table(
    data,
    index=["Component"],
    columns=["Product Code"],
    values=["Serial Number"],
    aggfunc="count",
    sort=True,
    margins=True,
)
pivot_component.to_excel(writer, sheet_name="pivot_component")
writer.save()
writer.close()


# 创建一个边框样式
border_style = Border(
    left=Side(style="thin", color=colors.BLACK),
    right=Side(style="thin", color=colors.BLACK),
    top=Side(style="thin", color=colors.BLACK),
    bottom=Side(style="thin", color=colors.BLACK),
)
alignment_style = Alignment(
    horizontal="left",  # 水平对齐，可选general、left、center、right、fill、justify、centerContinuous、distributed
    vertical="center",  # 垂直对齐， 可选top、center、bottom、justify、distributed
    text_rotation=0,  # 字体旋转，0~180整数
    wrap_text=False,  # 是否自动换行
    shrink_to_fit=False,  # 是否缩小字体填充
    indent=0,  # 缩进值
)

# 创建一个 Workbook 对象
# workbook = Workbook()
workbook = load_workbook(fullname)
# 遍历所有的 worksheet
for sheet in workbook.worksheets:
    # 遍历所有的行和列
    for row in sheet.rows:
        for cell in row:
            # 应用边框样式
            cell.border = border_style
            cell.alignment = alignment_style

# 保存 Workbook
workbook.save(fullname)


class CXlAutofit:
    # 生成列名字典，只是为了方便修改列宽时指定列，key:数字，从1开始；value:列名，从A开始
    def get_num_colnum_dict(self):
        """
        :return: 返回字典：{1:'A', 2:'B', ...... , 52:'AZ'}
        """
        num_str_dict = {}
        A_Z = [chr(a) for a in range(ord("A"), ord("Z") + 1)]
        AA_AZ = ["A" + chr(a) for a in range(ord("A"), ord("Z") + 1)]
        A_AZ = A_Z + AA_AZ
        for i in A_AZ:
            num_str_dict[A_AZ.index(i) + 1] = i
        return num_str_dict

    # 自适应列宽
    def style_excel(self, excel_name: str, sheet_name: str):
        """
        :param sheet_name:  excel中的sheet名
        :return:
        """
        # 打开excel
        wb = openpyxl.load_workbook(excel_name)
        # 选择对应的sheet
        sheet = wb[sheet_name]
        # 获取最大行数与最大列数
        max_column = sheet.max_column
        max_row = sheet.max_row

        # 将每一列，单元格列宽最大的列宽值存到字典里，key:列的序号从1开始(与字典num_str_dic中的key对应)；value:列宽的值
        max_column_dict = {}

        # 生成列名字典，只是为了方便修改列宽时指定列，key:数字，从1开始；value:列名，从A开始
        num_str_dict = self.get_num_colnum_dict()

        # 遍历全部列
        for i in range(1, max_column + 1):
            # 遍历每一列的全部行
            for j in range(1, max_row + 1):
                column = 0
                # 获取j行i列的值
                sheet_value = sheet.cell(row=j, column=i).value
                # 通过列表生成式生成字符列表，将当前获取到的单元格的str值的每一个字符放在一个列表中（列表中一个元素是一个字符）
                sheet_value_list = [k for k in str(sheet_value)]
                # 遍历当前单元格的字符列表
                for v in sheet_value_list:
                    # 判定长度，一个数字或一个字母，单元格列宽+=1.1，其它+=2.2（长度可根据需要自行修改，经测试一个字母的列宽长度大概为1）
                    if v.isdigit() == True or v.isalpha() == True:
                        column += 1.1
                    else:
                        column += 2.2
                # 当前单元格列宽与字典中的对比，大于字典中的列宽值则将字典更新。如果字典没有这个key，抛出异常并将值添加到字典中
                try:
                    if column > max_column_dict[i]:
                        max_column_dict[i] = column
                except Exception as e:
                    max_column_dict[i] = column
        # 此时max_column_dict字典中已存有当前sheet的所有列的最大列宽值，直接遍历字典修改列宽
        for key, value in max_column_dict.items():
            sheet.column_dimensions[num_str_dict[key]].width = value
        # 保存
        wb.save(excel_name)


# 调用方法 实例化类
Entity = CXlAutofit()
# 传入参数：Excel名称，需要设置列宽的Sheet名称
Entity.style_excel(fullname, "triage_raw_data")
Entity.style_excel(fullname, "pivot_qty")
Entity.style_excel(fullname, "pivot_sn")
Entity.style_excel(fullname, "pivot_component")


# # with xw.App(visible=False, add_book=False) as app:
# #     workbook = app.books.open(fullname)
# #     worksheet = workbook.sheets
# #     for i in worksheet:
# #         i.autofit()
# #     workbook.save()
# #     workbook.close()


# workbook = openpyxl.load_workbook(fullname)

# worksheet_names = workbook.sheetnames

# for worksheet_name in worksheet_names:
#     worksheet = workbook[worksheet_name]
#     print(worksheet)
# # #for col in ws.columns:
# # #    max_length = 0
# # #    column = col[0].column_letter
# # #    for cell in col:
# # #        try:
# # #            if len(str(cell.value)) > max_length:
# # #                max_length = len(str(cell.value))
# # #        except:
# # #            pass
# # #    adjusted_width = (max_length + 2)
# # #    ws.column_dimensions[column].width = adjusted_width
#     for column_cells in worksheet.columns:
#         #for i in range(worksheet.max_column):
#         length = max(len(str(cell.value)) for cell in column_cells)
#         #print(worksheet.columns)
#         print(type(worksheet.columns))
#         #print(20*'*')
#         #print(worksheet)
#         #print(length)
#         print(get_column_letter(column_cells))
#         #worksheet.column_dimensions[get_column_letter(column_cells+1)].width = length + 2
# workbook.save(fullname)
